﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Common.Views;
using Common;
using iTextSharp.text;
using iTextSharp.text.pdf;
using System.IO;
using System.Net.Mail;
using System.Net;

namespace DataAccess
{
    public class OrdersRepository: ConnectionClass
    {
        public OrdersRepository() : base() { }

        public OrdersRepository(bool isAdmin) : base(isAdmin) { }

        public void AddOrder(Order o)
        {
            Entity.Orders.AddObject(o);
            Entity.SaveChanges();
        }

        public void AddOrderDetails(OrderDetail od)
        {
            Entity.OrderDetails.AddObject(od);
            Entity.SaveChanges();
        }

        public IQueryable<OrdersView> GetProductsBoughtByUser(string username)
        {
            return (
                from p in Entity.Products
                join od in Entity.OrderDetails
                on p.ProductID equals od.ProductID
                join o in Entity.Orders
                on od.OrderID equals o.OrderID

                where o.Username == username

                select new OrdersView()
                {
                    OrderID = o.OrderID,
                    ProductID = p.ProductID,
                    ProductName = p.ProductName,
                    Quantity = od.ProductQty,
                    OrderDate = o.DatePurchased,
                    ImageLink = p.ImageLink,
                    UnitPrice = p.Price,
                    WarrantyExpiryDate = od.WarrantyExpiryDate
                }).AsQueryable();
        }

        public IQueryable<StatisticalView> MostPurchasedItem()
        {
            return (
                    from p in Entity.Products
                    join od in Entity.OrderDetails
                    on p.ProductID equals od.ProductID
                    join o in Entity.Orders
                    on od.OrderID equals o.OrderID
                    group od by new { od.ProductID, p.ProductName, p.Features, p.ImageLink } into product
                    
                    select new StatisticalView()
                    {
                        ProductID = product.Key.ProductID,
                        ProductName = product.Key.ProductName,
                        ImageLink = product.Key.ImageLink,
                        Features = product.Key.Features,
                        TotalQty = product.Sum(x => x.ProductQty)
                    }

                    ).OrderByDescending(x => x.TotalQty).Take(1);
        }

        public IQueryable<StatisticalView> HighlyRatedItem()
        {
            return (
                    from p in Entity.Products
                    join od in Entity.OrderDetails
                    on p.ProductID equals od.ProductID
                    join o in Entity.Orders
                    on od.OrderID equals o.OrderID
                    join pr in Entity.ProductRatings
                    on p.ProductID equals pr.ProductID
                    group pr by new { od.ProductID, p.ProductName, p.Features, p.ImageLink } into product

                    select new StatisticalView()
                    {
                        ProductID = product.Key.ProductID,
                        ProductName = product.Key.ProductName,
                        ImageLink = product.Key.ImageLink,
                        Features = product.Key.Features,
                        AverageRating = product.Average(x => x.RatingNo)

                    }

                    ).OrderByDescending(x => x.AverageRating).Take(1);
        }

        public IQueryable<StatisticalView> HighestNumOfFaults()
        {
            return (from p in Entity.Products
                join f in Entity.Faults
                on p.ProductID equals f.ProductID
                group f by new { f.ProductID, p.ProductName, p.Features, p.ImageLink, p.Price } into product
                select new StatisticalView()
                {
                    ProductID = product.Key.ProductID,
                    ProductName = product.Key.ProductName,
                    ImageLink = product.Key.ImageLink,
                    Features = product.Key.Features,
                    FaultNo = product.Count()
                }
                ).OrderByDescending(x => x.FaultNo).Take(10);
        }

        public IQueryable<StatisticalView> LeastNumOfFaults()
        {
            return (from p in Entity.Products
                    join f in Entity.Faults
                    on p.ProductID equals f.ProductID
                    group f by new { f.ProductID, p.ProductName, p.Features, p.ImageLink, p.Price } into product
                    select new StatisticalView()
                    {
                        ProductID = product.Key.ProductID,
                        ProductName = product.Key.ProductName,
                        ImageLink = product.Key.ImageLink,
                        Features = product.Key.Features,
                        FaultNo = product.Count()
                    }
                ).OrderBy(x => x.FaultNo).Take(10);
        }

        public Document CreatePDF(IQueryable<OrdersView> products, MemoryStream ms)
        {
            Document doc = new Document();
            PdfWriter writer = PdfWriter.GetInstance(doc, ms);
            doc.Open();
            PdfPTable table = new PdfPTable(6); //New table with 6 columns

            PdfPCell header = new PdfPCell(new Phrase("Order Details"));
            header.Colspan = 6; //Merge the cell
            header.HorizontalAlignment = 1; //Center align
            header.Border = 0; 
            table.AddCell(header);

            //Adding table headers
            table.AddCell("Product ID");
            table.AddCell("Product Name");
            table.AddCell("Quantity");
            table.AddCell("Price per product");
            table.AddCell("Date Purchased");
            table.AddCell("Warranty Expiry Date");

            //Adding details for each product
            foreach (OrdersView p in products)
            {
                table.AddCell(p.ProductID.ToString());
                table.AddCell(p.ProductName);
                table.AddCell(p.Quantity.ToString());
                table.AddCell("€" + p.UnitPrice.ToString());
                table.AddCell(p.OrderDate.ToShortDateString());
                table.AddCell(p.WarrantyExpiryDate.ToShortDateString());
            }

            doc.Add(table);
            writer.CloseStream = false;
            doc.Close();
            ms.Position = 0;

            return doc;
        }

        public void SaveDetails(string username)
        {
            string email = new UsersRepository().GetUserEmail(username);
            Guid pdfName = Guid.NewGuid();
            IQueryable<OrdersView> products = GetProductsBoughtByUser(username);

            MemoryStream ms = new MemoryStream();
            Document doc = CreatePDF(products, ms);

            MailMessage mm = new MailMessage();
            mm.To.Add(email);
            mm.From = new MailAddress("fabulousfashion00@gmail.com");
            mm.Subject = "Order Statement";
            mm.Body = "Attached please find your order details.<br/>";
            mm.Body += "Yours sincerely, <br/> <b>Electros Ltd</b>";
            mm.Attachments.Add(new Attachment(ms, pdfName + ".pdf"));
            mm.IsBodyHtml = true;

            SmtpClient client = new SmtpClient("smtp.gmail.com", 587);
            client.EnableSsl = true;
            client.DeliveryMethod = SmtpDeliveryMethod.Network;
            client.Credentials = new NetworkCredential("fabulousfashion00@gmail.com", "fabfashadmin");
            client.Send(mm);
        }
    }
}
